package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

import co.gov.medellin.ssti.sub.model.dao.IAreaDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.AreaVO;

public class AreaDAOImpl extends BaseDAOImpl implements IAreaDAO {

	public AreaDAOImpl(Connection con) {
		super(con);
	}

	public Collection<AreaVO> findAreas(String strNombreArea)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer(
				"SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
						+ " FROM TCS_BEN_AREA WHERE AREA_CODIGO IS NULL ");
		List<Object> parameters = new ArrayList<Object>();

		if ((Object) strNombreArea != null) {
			query.append(" AND NOMBRE LIKE UPPER(?) ");
			parameters.add("%" + strNombreArea + "%");
		}
		query.append(" ORDER BY NOMBRE");

		Collection<AreaVO> lListaAreas = new LinkedList<AreaVO>();
		try {
			pstmt = conn.prepareCall(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCode(new Long(rst.getString("CODIGO")));
				objAreaVO.setCodearea(rst.getString("AREA_CODIGO"));
				objAreaVO.setDescripcion(rst.getString("DESCRIPCION"));
				objAreaVO.setNombre(rst.getString("NOMBRE"));
				lListaAreas.add(objAreaVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lListaAreas;
	}

	public Collection<AreaVO> findAllAreas() throws DAOException {
		Statement stmt = null;
		ResultSet rst = null;

		String query = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
				+ " FROM TCS_BEN_AREA "
				+ " WHERE AREA_CODIGO IS NULL "
				+ " ORDER BY NOMBRE";

		List<AreaVO> lListaAreas = new LinkedList<AreaVO>();
		try {
			stmt = conn.createStatement();
			rst = stmt.executeQuery(query);

			while (rst.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCode(new Long(rst.getString("CODIGO")));
				objAreaVO.setCodearea(rst.getString("AREA_CODIGO"));
				objAreaVO.setDescripcion(rst.getString("DESCRIPCION"));
				objAreaVO.setNombre(rst.getString("NOMBRE"));
				lListaAreas.add(objAreaVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, stmt);
		}
		return lListaAreas;
	}

	public Collection<AreaVO> findArea(String strCode) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
				+ " FROM TCS_BEN_AREA WHERE CODIGO = ? ";

		List<AreaVO> lListaAreas = new LinkedList<AreaVO>();

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, strCode);
			rst = pstmt.executeQuery();

			if (rst.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCode(new Long(rst.getString("CODIGO")));
				objAreaVO.setCodearea(rst.getString("AREA_CODIGO"));
				objAreaVO.setDescripcion(rst.getString("DESCRIPCION"));
				objAreaVO.setNombre(rst.getString("NOMBRE"));
				lListaAreas.add(objAreaVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lListaAreas;
	}

	public int insertArea(String[] Arr) throws DAOException {
		PreparedStatement pstmt = null;

		String query = "INSERT INTO TCS_BEN_AREA("
				+ " CODIGO, NOMBRE, DESCRIPCION, AREA_CODIGO, VIGENCIA, usuario_crea, fecha_crea, terminal_crea, "
				+ " usuario_modifica, fecha_modifica, terminal_modifica) "
				+ " VALUES(SCS_BEN_AREA.NextVal, upper(?), upper(?), ?, 'A', ?, TO_DATE(?,'dd/mm/yyyy'), ?, "
				+ " ?, TO_DATE(?,'dd/mm/yyyy'), ?)";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, Arr[0]);// strNombre
			pstmt.setString(2, Arr[1]);// strDescripcion
			pstmt.setString(3, Arr[2]);// strsubarea
			pstmt.setString(4, Arr[3]);// user.getName()
			pstmt.setString(5, Arr[4]);// dateFormat.format(c.getTime())
			pstmt.setString(6, Arr[5]);// strTerminal
			pstmt.setString(7, Arr[6]);// user.getName()
			pstmt.setString(8, Arr[7]);// dateFormat.format(c.getTime())
			pstmt.setString(9, Arr[8]);// strTerminal

			return pstmt.executeUpdate();

		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(pstmt);
		}
	}

	public int updateArea(String[] Arr, String strCode) throws DAOException {
		PreparedStatement pstmt = null;

		String query = "UPDATE TCS_BEN_AREA SET NOMBRE = UPPER(?), "
				+ " DESCRIPCION = UPPER(?), usuario_modifica = ?, "
				+ " fecha_modifica = TO_DATE(?,'dd/mm/yyyy'), "
				+ " terminal_modifica = ? WHERE CODIGO = ?";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, Arr[0]);// strNombre
			pstmt.setString(2, Arr[1]);// srtDescripcion
			pstmt.setString(3, Arr[2]);// user.getName()
			pstmt.setString(4, Arr[3]);// dateFormat.format(c.getTime())
			pstmt.setString(5, Arr[4]);// strTerminal
			pstmt.setString(6, strCode);//

			return pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(pstmt);
		}
	}

	public int updateSubArea(String[] Arr, String strCode) throws DAOException {
		PreparedStatement pstmt = null;

		String query = " UPDATE TCS_BEN_AREA SET NOMBRE = UPPER(?), "
				+ " DESCRIPCION = UPPER(?), AREA_CODIGO = ?, "
				+ " usuario_modifica = ?, fecha_modifica = TO_DATE(?,'dd/mm/yyyy'), "
				+ " terminal_modifica = ? WHERE CODIGO = ? ";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, Arr[0]);// strNombre
			pstmt.setString(2, Arr[1]);// strDescripcion
			pstmt.setString(3, Arr[2]);// strSubarea
			pstmt.setString(4, Arr[3]);// user.getName()
			pstmt.setString(5, Arr[4]);// dateFormat.format(c.getTime())
			pstmt.setString(6, Arr[5]);// strTerminal
			pstmt.setString(7, strCode);

			return pstmt.executeUpdate();

		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(pstmt);
		}
	}

	public Collection<AreaVO> findAllSubAreas(String strCodeArea)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO, "
				+ " (SELECT NOMBRE FROM TCS_BEN_AREA WHERE CODIGO = ? ) AREA "
				+ " FROM TCS_BEN_AREA "
				+ " WHERE AREA_CODIGO = ? "
				+ " ORDER BY NOMBRE";

		List<AreaVO> lListaAllSubAreas = new LinkedList<AreaVO>();

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, strCodeArea);
			pstmt.setString(2, strCodeArea);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				AreaVO objAreaVO = new AreaVO();
				objAreaVO.setCodearea(rst.getString("AREA_CODIGO"));
				objAreaVO.setDescripcion(rst.getString("DESCRIPCION"));
				objAreaVO.setNombre(rst.getString("AREA"));
				objAreaVO.setNombresubarea(rst.getString("NOMBRE"));
				objAreaVO.setCode(new Long(rst.getString("CODIGO")));
				lListaAllSubAreas.add(objAreaVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}

		return lListaAllSubAreas;
	}

	/*
	 * public Collection<AreaVO> findAllSubSecretarias(String strCodeArea)
	 * throws DAOException { PreparedStatement pstmt = null; ResultSet rst =
	 * null;
	 * 
	 * String query =
	 * "SELECT CODIGO,NOMBRE,DESCRIPCION,NVL(AREA_CODIGO,'') AREA_CODIGO, " +
	 * " (SELECT NOMBRE FROM TCS_BEN_AREA WHERE CODIGO = ? ) AREA " +
	 * " FROM TCS_BEN_AREA " + " WHERE AREA_CODIGO = ? " + " ORDER BY NOMBRE";
	 * 
	 * List<AreaVO> lListaAllSubAreas = new LinkedList<AreaVO>();
	 * 
	 * try { pstmt = conn.prepareStatement(query); pstmt.setString(1,
	 * strCodeArea); pstmt.setString(2, strCodeArea); rst =
	 * pstmt.executeQuery();
	 * 
	 * while (rst.next()) { AreaVO objAreaVO = new AreaVO();
	 * objAreaVO.setCodearea(rst.getString("CODIGO"));
	 * objAreaVO.setNombresubarea(rst.getString("NOMBRE"));
	 * lListaAllSubAreas.add(objAreaVO); } } catch (SQLException e) { throw new
	 * DAOException(e); } finally { close(rst, pstmt); }
	 * 
	 * return lListaAllSubAreas; }
	 */

	public Collection<AreaVO> findAreasAuthorized(String strCodeUsu,
			String strGroupUser) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = null;
		int tipoQuery = 0;
		if (strGroupUser.equals("ADMINISTRADOR GENERAL")
				|| strGroupUser.equals("SUPER USUARIO")) {
			query = "SELECT distinct area.CODIGO codigo, area.NOMBRE nombre "
					+ " FROM tcs_ben_area area "
					+ " WHERE area.AREA_CODIGO is null "
					+ " ORDER BY area.NOMBRE ";
			tipoQuery = 1;
		} else {
			query = "SELECT a.codigo codigo, a.nombre nombre "
					+ " FROM tcs_ben_area a "
					+ " WHERE a.codigo = ANY(SELECT pro.area_codigo "
					+ " FROM TCS_BEN_PROGRAMA pro "
					+ " WHERE (pro.vigencia = 'A' OR sysdate BETWEEN pro.DESDE_VIGENCIA AND pro.HASTA_VIGENCIA)  "
					+ " AND pro.usu_codigo = ? "
					+ " UNION select area.area_codigo "
					+ " FROM tcs_ben_subprograma sub, tcs_ben_area area "
					+ " WHERE sub.area_codigo = area.codigo "
					+ " AND (sub.vigencia = 'A' OR sysdate between sub.DESDE_VIGENCIA and  sub.HASTA_VIGENCIA) "
					+ " AND sub.usu_codigo = ? "
					+ " UNION SELECT campo_n1 "
					+ " FROM paoyer.tcs_usuario "
					+ " WHERE campo_n1 IS NOT NULL "
					+ " AND codigo = ? "
					+ " UNION  SELECT area.area_codigo "
					+ " FROM tcs_ben_subprograma_usuario subus, tcs_ben_subprograma sub, tcs_ben_area area "
					+ " WHERE subus.SBPR_CODIGO = sub.CODIGO "
					+ " AND sub.area_codigo = area.codigo "
					+ " AND (sub.vigencia = 'A' OR sysdate BETWEEN sub.DESDE_VIGENCIA AND sub.HASTA_VIGENCIA) "
					+ " AND subus.USU_CODIGO = ? ) " + " ORDER BY a.NOMBRE ";
			tipoQuery = 2;
		}

		ArrayList<AreaVO> secretarias = new ArrayList<AreaVO>();

		try {
			pstmt = conn.prepareStatement(query);
			// si la consulta es la segunda, con parametros.
			if (tipoQuery == 2) {
				pstmt.setString(1, strCodeUsu);
				pstmt.setString(2, strCodeUsu);
				pstmt.setString(3, strCodeUsu);
				pstmt.setString(4, strCodeUsu);
			}
			rst = pstmt.executeQuery();

			while (rst.next()) {
				AreaVO area = new AreaVO();
				area.setCodearea(rst.getString("codigo"));
				area.setNombre(rst.getString("nombre"));
				secretarias.add(area);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}

		return secretarias;
	}

	public Collection<AreaVO> findAreasAuthorizedWithGestor(String strCodeUsu,
			String strGroupUser) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = null;
		boolean hasParameters = false;

		if (strGroupUser.equals("ADMINISTRADOR GENERAL")
				|| strGroupUser.equals("SUPER USUARIO")) {
			query = "SELECT distinct sec.codigo, sec.nombre FROM "
					+ " tcs_ben_area sec, tcs_ben_area area, "
					+ " tcs_ben_subprograma sub, "
					+ " tcs_ben_subp_atr_beneficiario sab WHERE "
					+ " sec.codigo = area.area_codigo "
					+ " AND area.codigo = sub.area_codigo "
					+ " AND sub.codigo = sab.sbpr_codigo "
					+ " AND UPPER(TRIM(sab.nombre)) = 'GESTOR' "
					+ " ORDER BY nombre ";
		} else {
			query = "SELECT sec.codigo codigo, sec.nombre nombre\n"
					+ " FROM tcs_ben_area sec\n" + "WHERE sec.codigo IN (\n"
					+ "	SELECT area.area_codigo\n" + "		FROM\n"
					+ "			tcs_ben_area area,\n"
					+ "			tcs_ben_subp_atr_beneficiario sab,\n"
					+ "			tcs_ben_subprograma sub,\n"
					+ "			tcs_ben_subprograma_usuario subus\n" + "		WHERE\n"
					+ "			area.codigo = sub.area_codigo\n"
					+ "			AND upper(trim(sab.nombre)) = 'GESTOR'\n"
					+ "			AND sab.sbpr_codigo = sub.codigo\n"
					+ "			AND sub.codigo = subus.sbpr_codigo\n"
					+ "			AND subus.usu_codigo = ? " + "\n" + "	UNION\n"
					+ "	SELECT subus.campo_n1\n" + "		FROM\n"
					+ "			tcs_ben_subp_atr_beneficiario sab,\n"
					+ "			tcs_ben_subprograma sub,\n"
					+ "			tcs_ben_area area,\n"
					+ "			paoyer.tcs_usuario subus\n" + "		WHERE\n"
					+ "			upper(trim(sab.nombre)) = 'GESTOR'\n"
					+ "			AND sab.sbpr_codigo = sub.codigo\n"
					+ "			AND sub.area_codigo = area.codigo\n"
					+ "			AND area.area_codigo = subus.campo_n1\n"
					+ "			AND subus.codigo = ? " + "\n" + "	)\n"
					+ "order by sec.nombre";

			hasParameters = true;
		}

		ArrayList<AreaVO> secretarias = new ArrayList<AreaVO>();

		try {
			pstmt = conn.prepareStatement(query);
			if (hasParameters) {
				pstmt.setString(1, strCodeUsu);
				pstmt.setString(2, strCodeUsu);
			}
			rst = pstmt.executeQuery();

			while (rst.next()) {
				AreaVO area = new AreaVO();
				area.setCodearea(rst.getString("CODIGO"));
				area.setNombre(rst.getString("NOMBRE"));
				secretarias.add(area);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return secretarias;
	}

	public AreaVO findAreaByProg(String strCodeProg) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT area.CODIGO CODEAREA, area.NOMBRE NAMEAREA "
				+ " FROM tcs_ben_programa prog, tcs_ben_area area "
				+ " WHERE prog.AREA_CODIGO = area.CODIGO "
				+ " AND prog.codigo = ? ";
		AreaVO areaVO = new AreaVO();
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, strCodeProg);
			rst = pstmt.executeQuery();

			if (rst.next()) {
				areaVO.setNombre(rst.getString("NAMEAREA"));
				areaVO.setCode(new Long(rst.getString("CODEAREA")));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return areaVO;
	}
}
